<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/*SELECT id, agent_name, Operation_Date, transfer_code, Order_Status1, from_currency, to_currency, transaction_amount, amount_received,
	sender_reference, exchange_rate, Comments   FROM km_msb_form_inward k WHERE transfer_code="PCT" LIMIT 0,1000
	---payments from perfect money
	*/
class Ewallet_model extends CI_Model
{
	protected $db2;
	public $tbl_user = 'users';
	public $tbl_wallet = 'wallets';
	public $tbl_wammounts = 'wallet_ammounts';
	
	function __construct(){
		parent::__construct();
		$this->db2 = $this->load->database('ewallet', TRUE, TRUE);
	}

	
	function user_login($data)
	{
		$this->db2->from($this->tbl_user);
		$this->db2->where($data);
		$query = $this->db2->get();
		if($query->num_rows()==1)
		{
			$this->db2->select("CURRENT_LOGIN");
			$this->db2->from($this->tbl_user);
			$this->db2->where("USER_ID", $data["USER_ID"]);
			$q = $this->db2->get();
			$res = $q->result();
			
			$current_time = $res[0]->CURRENT_LOGIN; 
			$last_time = $current_time;
			$current_time = date("Y-m-j H:i:s");
			$arr = array(
				"CURRENT_LOGIN" => $current_time,
				"LAST_LOGIN" => $last_time,
				"LAST_IP" => $this->input->ip_address()
			);
			
			$this->db2->where("USER_ID", $data["USER_ID"]);
			$this->db2->update($this->tbl_user, $arr);
			
			$this->db2->from($this->tbl_user);
			$this->db2->where($data);
			$query = $this->db2->get();
			return $query->row_array();
		}
		return false;
	}


	function get_countries(){
		$this->db2->select('ID, NAME, AREA_CODE');
		$this->db2->from('countries');
		$query = $this->db2->get();
		return $query->result();
	}
	
	function get_currencies(){
		$this->db2->select('ID, C_SYMBOL');
		$this->db2->from('currencies');
		$query = $this->db2->get();
		return $query->result_array();
	}
	
	function get_currency($cID) {
		if($cID){
			$this->db2->select('C_SIGN, C_SYMBOL');
			$q = $this->db2->get_where('currencies', array('ID' => $cID));
			$result = $q->row_array();
			return $result;
		}
		else{
			return false;
		}
	}
	
	function get_wallets($userID) {
		if($userID){
			$query = $this->db2->query("SELECT  w.ID, w.NAME, w.WALLET_CODE, w.DATE_OPENED, u.BALANCE_CURR_ID, c.C_SYMBOL, c.C_SIGN,
				ROUND(SUM(wa.AMMOUNT * cr.RATE), 2) AS BALANCE
		FROM ((((wallets w LEFT JOIN wallet_ammounts wa ON w.ID=wa.WALLET_ID) 
		LEFT JOIN currency_rates cr ON cr.BCODE=wa.CURRENCY_ID ) 

		LEFT JOIN users u ON cr.FCODE=u.BALANCE_CURR_ID)
		LEFT JOIN currencies c ON u.BALANCE_CURR_ID=c.ID)
		WHERE w.USER_ID=$userID AND cr.FCODE=u.BALANCE_CURR_ID
		GROUP BY w.ID;");
			if($query->num_rows() > 0)
			{
				return $query->result_array();
			}
			else{
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	/**
	 * 
	 * Gets the agregate data from wallets and wallet_ammounts table.
	 * @param int $userID
	 * @return array of wallets and their containing purses with details
	 */	
	function get_purses($userID) {
		if($userID){
			$query = $this->db2->query("SELECT wa.ID, wa.CURRENCY_ID, wa.WALLET_ID, w.NAME, w.WALLET_CODE, c.C_SYMBOL, c.C_SIGN, ROUND(wa.AMMOUNT, 2) AS AMMOUNT
		FROM ((wallet_ammounts wa LEFT JOIN wallets w ON wa.WALLET_ID=w.ID) 
		LEFT JOIN currencies c ON wa.CURRENCY_ID=c.ID)
		WHERE w.USER_ID=$userID 
		ORDER BY w.ID;");
			if($query->num_rows() > 0)
			{
				return $query->result_array();
			}
			else{
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	function get_wallet($userID, $walletCode = NULL, $walletID = NULL) {
		if($userID){
			$this->db2->select('ID, NAME, WALLET_CODE, DATE_OPENED');
			$this->db2->from('wallets');
			if($walletCode){
				$cond = array('USER_ID' => $userID, 'WALLET_CODE' => $walletCode);
			}
			elseif ($walletID){
				$cond = array('USER_ID' => $userID, 'ID' => $walletID);
			}
			else{
				return false;
			}
			$this->db2->where($cond);
			$query = $this->db2->get();
//			print $this->db2->last_query();
			return $query->row_array();
		}
		else{
			return false;
		}
	}

	function get_wcurrencies($walletID, $purseID = NULL) {
		if($walletID){
			$this->db2->select('c.ID, w.ID AS ID_AMMOUNT, c.C_SYMBOL, w.AMMOUNT, c.C_SIGN');
			$this->db2->from('wallet_ammounts w');
			$this->db2->join('currencies c', 'w.CURRENCY_ID = c.ID', 'left');
			if($purseID){
				$cond = array('w.WALLET_ID' => $walletID, 'w.CURRENCY_ID' => $purseID);
			}
			else{
				$cond = array('w.WALLET_ID' => $walletID);
			}
			$this->db2->where($cond);
			$query = $this->db2->get();
//			print "<br />sql:" . $this->db2->last_query();
			
			if($purseID){
				return $query->row_array();
			}
			else{
				return $query->result_array();
			}
		}
		else{
			return false;
		}
	}


	function check_email_exist($email = NULL){
		$this->db2->where("USER_ID", $email);
		$this->db2->from($this->tbl_user);
		$count = $this->db2->count_all_results();

		if($count > 0){
			return -1;
		}
		else{
			return 1;
		}
	}

	function check_country_exist($countryID = NULL){
		$this->db2->where("COUNTRY_CODE", $countryID);
		$this->db2->from($this->tbl_user);
		$count = $this->db2->count_all_results();

		if($count > 0){
			return -1;
		}
		else{
			return 1;
		}
	}
	
	function save_exchange($walletID, $currencyID, $toCurrencyID, $ammount = 0, $ex_ammount = 0){
		if($walletID && $currencyID && $toCurrencyID && $ammount && $ex_ammount){
			$this->db2->trans_begin();//start transaction
			
			$this->db2->select("ID, AMMOUNT");
			$this->db2->from("wallet_ammounts");
			$cond = array('WALLET_ID' => $walletID, "CURRENCY_ID" => $currencyID);
			$this->db2->WHERE($cond);
			$query = $this->db2->get();
			$res = $query->row_array();
			if($res["AMMOUNT"] > $ammount){
				$from_wa_id = $res["ID"];
				// TODO VERIFIY IF THE DESTINATION WALLET HAS THE NECESSARY CURRENCY; IF NOT THEN CREATE IT
				$this->db2->select("COUNT(ID) AS COUNT");
				$this->db2->from("wallet_ammounts");
				$cond = array('WALLET_ID' => $walletID, "CURRENCY_ID" => $toCurrencyID);
				$this->db2->WHERE($cond);
				$query = $this->db2->get();
				$result = $query->row_array();
				if($result["COUNT"] == 1){
					/*the destination wallet has the desired currency*/
					$this->get_from_wammount($walletID, $currencyID, $ammount);
					$this->add_to_wammount($walletID, $toCurrencyID, $ex_ammount);
				}
				elseif($result["COUNT"] == 0) {
					/*the destination wallet doesn't have the desired currency - in this case have to be created*/
					$wammount = new Wallet_currency();
					$wammount->WALLET_ID = $walletID;
					$wammount->CURRENCY_ID = $toCurrencyID;
					$wammount->AMMOUNT = $ex_ammount;
					$this->db2->insert($this->tbl_wammounts, $wammount);
//					$insert_id = $this->db2->insert_id();
					$this->get_from_wammount($walletID, $currencyID, $ammount);
				}
				else {
					$this->db2->trans_rollback();
					return false;
				}
				
				$this->db2->select("ID");
				$this->db2->from("wallet_ammounts");
				$cond = array('WALLET_ID' => $walletID, "CURRENCY_ID" => $toCurrencyID);
				$this->db2->WHERE($cond);
				$query = $this->db2->get();
				$rez = $query->row_array();
								
				$trans_log = new Transfer();
				$trans_log->USER_ID = $this->session->userdata("ID");
				$trans_log->FROM_WA_ID = $from_wa_id;
				$trans_log->TO_WA_ID = $rez["ID"];
				$trans_log->AMMOUNT =  $ammount;
				$trans_log->TRANSFER_DATE =  date("Y-m-d H:i:s");
				$trans_log->OPERATION =  2;
				$trans_log->STATUS =  5;
				$this->log_transfer($trans_log);//logging the transfer
				
				$this->finish_transaction();
				return true;
			}
			else {
				return false;
			}
			$this->finish_transaction();//complete transaction
		}
		else{
			return false;
		}
	}
	
	function save_internal_transfer($walletID, $currencyID, $toWalletID, $ammount = 0){
		if($walletID && $currencyID && $ammount && $toWalletID){
			$this->db2->trans_begin();//start transaction
			
			$this->db2->select("ID, AMMOUNT");
			$this->db2->from("wallet_ammounts");
			$cond = array('WALLET_ID' => $walletID, "CURRENCY_ID" => $currencyID);
			$this->db2->WHERE($cond);
			$query = $this->db2->get();
			$res = $query->row_array();
			if($res["AMMOUNT"] > $ammount){
				$from_wa_id = $res["ID"];
				// TODO VERIFIY IF THE DESTINATION WALLET HAS THE NECESSARY CURRENCY; IF NOT THEN CREATE IT
				$this->db2->select("COUNT(ID) AS COUNT");
				$this->db2->from("wallet_ammounts");
				$cond = array('WALLET_ID' => $toWalletID, "CURRENCY_ID" => $currencyID);
				$this->db2->WHERE($cond);
				$query = $this->db2->get();
				$result = $query->row_array();
				if($result["COUNT"] == 1){
					/*the destination wallet has the desired currency*/
					$this->get_from_wammount($walletID, $currencyID, $ammount);
					$this->add_to_wammount($toWalletID, $currencyID, $ammount);
				}
				elseif($result["COUNT"] == 0) {
					/*the destination wallet doesn't have the desired currency - in this case have to be created*/
					$wammount = new Wallet_currency();
					$wammount->WALLET_ID = $toWalletID;
					$wammount->CURRENCY_ID = $currencyID;
					$wammount->AMMOUNT = $ammount;
					$this->db2->insert($this->tbl_wammounts, $wammount);
//					$insert_id = $this->db2->insert_id();
					$this->get_from_wammount($walletID, $currencyID, $ammount);
				}
				else {
					$this->db2->trans_rollback();
					return false;
				}
				
				$this->db2->select("ID");
				$this->db2->from("wallet_ammounts");
				$cond = array('WALLET_ID' => $toWalletID, "CURRENCY_ID" => $currencyID);
				$this->db2->WHERE($cond);
				$query = $this->db2->get();
				$rez = $query->row_array();
								
				$trans_log = new Transfer();
				$trans_log->USER_ID = $this->session->userdata("ID");
				$trans_log->FROM_WA_ID = $from_wa_id;
				$trans_log->TO_WA_ID = $rez["ID"];
				$trans_log->AMMOUNT =  $ammount;
				$trans_log->TRANSFER_DATE =  date("Y-m-d H:i:s");
				$trans_log->OPERATION =  3;
				$trans_log->STATUS =  5;
				$trans_log->COMMENT = $this->session->userdata("comment");
				$this->log_transfer($trans_log);//logging the transfer
				
				$this->finish_transaction();
				return true;
			}
			else {
				return false;
			}
			$this->finish_transaction();//complete transaction
		}
		else{
			return false;
		}
	}
	
	protected function finish_transaction(){
		if ($this->db2->trans_status() === FALSE)
		{
		    $this->db2->trans_rollback();
		}
		else
		{
		    $this->db2->trans_commit();
		}
	}
	
	function get_from_wammount($walletID, $currencyID, $ammount = 0){
		if($walletID && $currencyID && $ammount){
			if($this->db2->query("UPDATE wallet_ammounts SET AMMOUNT = AMMOUNT - " . $ammount . " WHERE WALLET_ID = " . $walletID . " AND CURRENCY_ID = " . $currencyID)){
				return true;
			}
			else return false;
			}
		else{
			return false;
		}
	}
	
	function add_to_wammount($walletID, $currencyID, $ammount = 0){
		if($walletID && $currencyID && $ammount){
			if($this->db2->query("UPDATE wallet_ammounts SET AMMOUNT = AMMOUNT + " . $ammount . " WHERE WALLET_ID = " . $walletID . " AND CURRENCY_ID = " . $currencyID)){
				return true;
			}
			else return false;
			}
		else{
			return false;
		}
	}
	
	function log_transfer($transfer){
		if($transfer){
			$this->db2->insert("transfers", $transfer);
			return $this->db2->insert_id();
		}
		return false;
	}
	
	function log_confirm_code($confirm_code){
		if($confirm_code){
			$this->db2->insert("confirmation_codes", $confirm_code);
			return $this->db2->insert_id();
		}
		return false;
	}
	

	function get_country($ID){
		if($ID){
			$this->db2->select('NAME');
			$q = $this->db2->get_where('countries', array('ID' => $ID));
			$result = $q->row();
			return $result->NAME;
		}
		else{
			return false;
		}
	}
	
	function get_wallets_codes() {
		$this->db2->select('WALLET_CODE');
		$q = $this->db2->from('wallets');
		$q = $this->db2->get();
		return $q->result();
	}
	
	function save($user_data){
		if($user_data){
			$this->db2->insert($this->tbl_user, $user_data);
			return $this->db2->insert_id();
		}
		else	return false;
	}
	
	function save_wallet($data) {
		if($data){
			$this->db2->insert($this->tbl_wallet, $data);
			return $this->db2->insert_id();
		}
		else	return false;
	}
	
	function save_wallet_title($walletCode, $userID, $title) {
		if($walletCode && $userID && $title){
			$cond = array("WALLET_CODE" => $walletCode, 
							"USER_ID" => $userID);
			$this->db2->where($cond);
			$data = array("NAME" => $title);
			return $this->db2->update($this->tbl_wallet, $data);
			 
		}
		else	return false;
	}

	function save_wallet_currency($data) {
		if($data){
			$this->db2->insert($this->tbl_wammounts, $data);
			return $this->db2->insert_id();
		}
		else	return false;
	}

	function remove_wallet_currency($data) {
		if($data){
			$res = $this->db2->delete($this->tbl_wammounts, $data);
			return $res;
		}
		else	return false;
	}

	function change_balance($userID, $currID) {
		if($userID && $currID){
			$cond = array("ID" => $userID);
			$this->db2->where($cond);
			$data = array("BALANCE_CURR_ID" => $currID);
			return $this->db2->update($this->tbl_user, $data);
		}
		else{
			return false;
		}
	}
	
	function get_balance($userID){
		if($userID){
			$query = $this->db2->query("SELECT  cr.FCODE, ROUND(SUM(wa.AMMOUNT * cr.RATE), 2) AS BALANCE
FROM ((((wallets w LEFT JOIN wallet_ammounts wa ON w.ID=wa.WALLET_ID) 
		LEFT JOIN currency_rates cr ON cr.BCODE=wa.CURRENCY_ID ) 
		LEFT JOIN currencies c ON wa.CURRENCY_ID=c.ID)
		LEFT JOIN users u ON cr.FCODE=u.BALANCE_CURR_ID)
	WHERE w.USER_ID=$userID AND cr.FCODE=u.BALANCE_CURR_ID
	GROUP BY u.ID");
			 if($query->num_rows() > 0)
			{
						return $query->row_array();
			}
			else{
				return false;
			}
		}
		else	return false;
	}
	
	function get_ex_rate($baseID, $foreID) {
		if($baseID && $foreID){
			$this->db2->select("RATE");
			$this->db2->from("currency_rates");
			$cond = array("BCODE" => $baseID,
							"FCODE" => $foreID);
			$this->db2->where($cond);
			$query = $this->db2->get();
			return $query->row_array();
		}
		else{
			return false;
		}
	}
}




class User_ewallet {
  public $ACCOUNT_TYPE = ""; 
  public $USER_TYPE= ""; 
  public $USER_ID = ""; 
  public $USER_PASSWD= ""; 
  public $COMP_NAME = ""; 
  public $FIRST_NAME = ""; 
  public $LAST_NAME = "";  
  public $COUNTRY_CODE = ""; 
  public $MOBILE_PHONE = "";  
  public $LAST_IP = "";  
}

class Wallet {
	public $USER_ID = ""; 
	public $NAME = ""; 
	public $WALLET_CODE = ""; 
	public $DATE_OPENED= ""; 
	public $BUSSINESS_INFO_ID = 0;   
}

class Wallet_currency {
	public $WALLET_ID = ""; 
	public $CURRENCY_ID = ""; 
	public $AMMOUNT = ""; 
}

class Transfer {
  public $USER_ID = ""; 
  public $FROM_WA_ID = ""; 
  public $TO_WA_ID = ""; 
  public $AMMOUNT = ""; 
  public $TRANSFER_DATE = "";  
  public $OPERATION = ""; 
  public $STATUS = "";  
  public $COMMENT = "";		
}

class Confirm_code {
  public $ACCOUNT_ID = ""; 
  public $CODE = ""; 
  public $AMMOUNT = 0; 
  public $SENDER_REFERENCE = ""; 
  public $PAY_SYSTEM = ""; 
  public $GEN_DATE = "";  
  public $STATUS_CODE = 0;  
}